package com.xiguaji.tiktok.v1.ins.media.repository.impl;

import com.xiguaji.boot.toolkit.jdbc.JdbcTemplateHelper;
import com.xiguaji.boot.toolkit.jdbc.Page;
import com.xiguaji.boot.toolkit.jdbc.ReflexUtil;
import com.xiguaji.tiktok.v1.ins.media.dto.InsAdMediaBrand;
import com.xiguaji.tiktok.v1.ins.media.dto.InsAdTagBrandMedia;
import com.xiguaji.tiktok.v1.ins.media.dto.InsMediaInfo;
import com.xiguaji.tiktok.v1.ins.media.repository.InsMediaExtRepository;
import org.springframework.util.StringUtils;

import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;


/**
 * 帖子扩展dao
 * @author Huangxin
 */
public class InsMediaExtRepositoryImpl implements InsMediaExtRepository {
    /**
     * 根据uid 查询广告帖子标记品牌帖子分页信息
     * @param uid
     * @param limitTime
     * @param category
     * @param page
     * @param size
     * @param order
     * @return
     */
    @Override
    public Page<InsAdTagBrandMedia> findAdTagBrandMediaByUid(long uid,long limitTime,String category,String order,int page,int size){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t3.shortcode,t3.is_video,t3.is_sponsored,t3.comment_count,t3.video_view_count,t3.like_count,t3.er * 100 er,t3.display_url,t3.caption ")
                .append(" FROM ( SELECT t2.tagger_uid,t1.caption,t1.is_video,t1.is_sponsored,t1.shortcode,t1.comment_count,t1.video_view_count,t1.like_count,t1.er,t1.display_url,t1.taken_time ")
                .append(" FROM ( SELECT shortcode,is_video,is_sponsored,caption,er,comment_count,video_view_count,like_count,display_url,taken_time ")
                .append(" FROM ins_media WHERE owner_uid = ? AND is_ad = 1 and taken_time > ? ) t1 ")
                .append(" LEFT JOIN ins_rel_media_tagger_user t2 ON t1.shortcode = t2.shortcode ) t3 ")
                .append(" INNER JOIN ins_blogger t4 ON t3.tagger_uid = t4.uid ")
                .append(" WHERE t4.is_business = 1 ");
        List<Object> params = new LinkedList<>();
        params.add(uid);
        params.add(limitTime);
        if(!StringUtils.isEmpty(category)){
            sql.append(" and overall_category_name = ?");
            params.add(category);
        }
        sql.append(" group by t3.shortcode,t3.is_video,t3.is_sponsored,t3.comment_count,t3.video_view_count,t3.like_count,t3.er,t3.display_url,t3.caption ");

        // 查询总数
        int totalRow = JdbcTemplateHelper.getJdbcTemplate().queryForObject("select count(1) from (" + sql + ") res ", params.toArray(), Integer.class);
        int totalPage = totalRow % size == 0 ? totalRow / size : totalRow / size + 1;

        sql.append(" order by " + order + " desc ,t3.shortcode limit ? , ? ");
        params.add((page - 1) * size);
        params.add(size);
        List<InsAdTagBrandMedia> dataList = JdbcTemplateHelper.findEntity(InsAdTagBrandMedia.class,sql.toString(),params.toArray());
        Page<InsAdTagBrandMedia> pageObject = new Page(dataList, page, size, totalPage, totalRow);
        return pageObject;
    }

    /**
     * 根据uid 查询广告帖子标记品牌分页信息
     * @param uid
     * @param limitTime
     * @param category
     * @param page
     * @param size
     * @return
     */
    @Override
    public Page<InsAdMediaBrand> findAdMediaBrandByUid(long uid,long limitTime, String category, int page, int size){
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT t4.uid,t4.username,t4.pic_url,t4.full_name,t4.category_name,t4.overall_category_name, avg(t3.er) * 100 avg_er,")
                .append(" count(1)tag_count FROM (SELECT t2.tagger_uid,er FROM (")
                .append(" SELECT shortcode,er FROM ins_media WHERE owner_uid = ? AND is_ad = 1 and taken_time > ? ) t1 ")
                .append(" LEFT JOIN ins_rel_media_tagger_user t2 ON t1.shortcode = t2.shortcode ) t3 ")
                .append(" INNER JOIN ins_blogger t4 ON t3.tagger_uid = t4.uid  ")
                .append(" WHERE t4.is_business = 1 ");
        List<Object> params = new LinkedList<>();
        params.add(uid);
        params.add(limitTime);
        if(!StringUtils.isEmpty(category)){
            sql.append(" and overall_category_name = ?");
            params.add(category);
        }
        sql.append(" GROUP BY t4.uid,t4.username,t4.pic_url,t4.full_name,t4.category_name ");

        // 查询总数
        int totalRow = JdbcTemplateHelper.getJdbcTemplate().queryForObject("select count(1) from (" + sql + ") res ", params.toArray(), Integer.class);
        int totalPage = totalRow % size == 0 ? totalRow / size : totalRow / size + 1;

        sql.append(" order by tag_count desc,username limit ? , ? ");
        params.add((page - 1)  * size);
        params.add(size);
        List<InsAdMediaBrand> dataList = JdbcTemplateHelper.findEntity(InsAdMediaBrand.class,sql.toString(),params.toArray());
        Page<InsAdMediaBrand> pageObject = new Page(dataList, page, size, totalPage, totalRow);
        return pageObject;
    }

    @Override
    public Page<InsMediaInfo> findPageByUid(Long uid, int page, int pageSize, Integer mediaType,Integer sponsored,Integer business,Integer product, long startTime, long endTime, int sort){
        String selectSql = "select m.shortcode,display_url,m.like_count+m.comment_count engagements,m.id,m.mid,m.score,m.like_count,m.comment_count,m.er,m.is_sponsored," +
                "case  when m.is_video = 0 THEN 1 when m.product_type = 'feed' THEN 2 when m.product_type = 'igtv' THEN 3 when m.product_type = 'clips'  THEN 4 ELSE 1 end mediaType ";
        String fromSql = " from ins_media m " +
                "where m.owner_uid = ? and m.taken_time between ? and ? ";

        if(null != mediaType){
            switch (mediaType){
                case 1:
                    fromSql += " and m.is_video = 0 ";
                    break;
                case 2:
                    fromSql += " and m.product_type = 'feed' and m.is_video = 1 ";
                    break;
                case 3:
                    fromSql+= " and m.product_type = 'igtv' and m.is_video = 1 ";
                    break;
                case 4:
                    fromSql+= " and m.product_type = 'clips' and m.is_video = 1 ";
                    break;
                default:
            }
        }


        if(null != sponsored && sponsored == 1){
            fromSql += " and m.is_sponsored = 1 ";
        }

        if(null != business && business == 1){
            fromSql += " and  m.is_ad = 1 ";
        }

        if(null != product && product == 1){
            fromSql += " and  m.product_tag_type in (1,2,3) ";
        }






        List<Object> params = new ArrayList<>();
        params.add(uid);
        params.add(startTime);
        params.add(endTime);
        String sortStr = "m.score";

        if(sort == 1){
            sortStr = "m.taken_time";
        }
        fromSql += " order by "+sortStr+" desc";
        return JdbcTemplateHelper.paginate(InsMediaInfo.class,page,pageSize,selectSql,fromSql,params.toArray());
    }
}
